<<<<<<< HEAD
<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Account_model extends CI_Model {
	
	function __construct(){
        parent::__construct();
    }
    
    
	function arrays($key){
    	$data = array(
    		'user_loan' 			=> array("0"=>"投资客", "1"=>"借款户"),
    		'user_self' 	=> array("0"=>"客户", "1"=>"马甲"),		
	        'account_type' 			=> array(
					TENDER=>'投标',RECHARGE=>'用户充值',BORROW_FEE=>'借款管理费',CASH_CANCEL=>'取消提现解冻',CASH_FROST=>'提现冻结',
					BORROW_SUCCESS=>'借款入帐',MARGIN=>'保证金',INVEST=>'扣除冻结款',FEE=>'充值手续费',AWARD_LOWER=>'扣除投标奖励',
					AWARD_ADD=>'投标奖励',REPAYMENT=>'借款户还款',INVEST_FALSE=>'投标失败资金返回',RECHARGE_FEE=>'提现手续费',COLLECTION=>'待收金额',
					BORROW_FROST=>'解冻借款担保金',INVEST_REPAYMENT=>'投资人回款',VIP=>'vip会员费',REALNAME=>'实名认证费用',RECHARGE_SUCCESS=>'提现审核通过',
					RECHARGE_FALSE=>'提现失败',SYSTEM_REPAYMENT=>'逾期系统还款',LATE_RATE=>'逾期利息扣除',INVEST_REPAYMENT_WY=>'客户提前还款违约',TICHENG=>'投标提成',
					LATE_COLLECTION=>'逾期收入',SCENE_AC=>'现场认证费用',VOUCH_ADVANCED=>'担保垫付扣费',BORROW_KOUHUI=>'借款人罚金扣回',VOUCH_AWARDPAY=>'担保奖励支付',
					VOUCH_AWARD=>'担保奖励接收',MARGIN_VOUCH=>'担保成功冻结5%',VIDEO=>'视频认证费用',TENDER_MANGE=>'利息管理费用',REPAYMENT_WY=>'提前还款违约',
					INVEST_REPAYMENT_WY=>'客户提前还款违约',AC_OTHER=>'其它扣费',CREDIT_CASH=>'积分兑现',OTHER_FEE=>'其他费用',RECHARGE_REWARD=>'线下充值奖励',
					REPAYMENT_TENDER_REWARD=>'回款再投标奖励',INVITE_REWARD=>'推荐奖励',INVEST_ROLLBACK_REPAYMENT=>"还款回滚操作",INVEST_SINGLE_FALSE=>'用户撤标',
	        		REPEAT_VERIFY_FREEZE=>'重复复审冻结',REPEAT_VERIFY_COLLECTION=>'重复复审待收',REG_MONEY=>'注册礼金',RECHARGE_CHINABANK_REWARD=>'网银在线充值奖励',EXP_MONEY=>'体验金',EXP_MONEY_DEDUCT=>'体验金扣除',
	        		CREDITOR_FEEEZE=>'购买债权资金冻结',CREDITOR_NOFEEEZE=>'购买债权成功扣除冻结款',CREDITOR_SUCCESS=>'出售债权成功入账',CREDITOR_COLL_DECR=>'出售债权待收减少',CREDITOR_COLL_INCR=>'购买债权待收增加',CREDITOR_FAIL=>'购买债权失败解除冻结金',CREDITOR_SUCC_FEE=>'出售债权成功扣除手续费',
	        		INVEST_CANCEL_COLL=>'撤销投标待收扣除',INVEST_CANCEL_ACC=>'撤销投标本金返回',INVEST_CANCEL_AWARD=>'撤销投标奖励扣除',INVEST_FANXIAN=>'投标返现奖励',REDPACKAGE=>'代金券'),
    	);
    	
    	if($key && isset($data[$key])){
    		return $data[$key];
    	}else{
    		return array();
    	}
    }
    
    function get_account_user_list($data){
    	$_select = "a.id, a.user_id, u.username,u.add_time,a.total, a.use_money,
					a.no_use_money, a.collection, a.red_money,t3.online_recharge,t6.cash_money,t6.recharge_fee,t6.count_cash";
    	
   		$_where = " where 1=1 ";
		if(isset($data['where']['username']) && $data['where']['username'] != ''){
			$_where .= " and u.username like '%".$data['where']['username']."%'";
		}
		
		$_limit = " limit ".$data['page_start'].",".$data['page_size'];
		
		$sql_result = "select $_select from rsh_account as a inner join rsh_user as u on a.user_id=u.user_id
						 left join (SELECT user_id,SUM(money) AS online_recharge ,COUNT(id) as count_recharge FROM rsh_account_recharge WHERE  FSTATUS=1 GROUP BY user_id) AS t3 ON a.user_id=t3.user_id
						 LEFT JOIN (SELECT user_id,SUM(total) AS cash_money ,SUM(fee) as recharge_fee,SUM(credited) as cash_success,COUNT(id) as count_cash  FROM rsh_account_cash WHERE  fstatus=1 GROUP BY user_id) AS t6 ON a.user_id=t6.user_id 
						 ";		
		$sql_result .= $_where;
		
		if(isset($data['limit']) && $data['limit'] == 'all'){
				$query = $this->db->query($sql_result);
				$data['account_list'] = $query->result_array();
				return $data;
		}
		
		$sql_result .= $_limit;
		$query = $this->db->query($sql_result);
		$data['account_list']  = $query->result_array();
		
		
		
		//统计可用金额的总额
		$sql_sum = "select sum(a.use_money) as total from rsh_account as a inner join rsh_user as u on a.user_id=u.user_id";
		$sql_sum .= $_where;
		
		$query = $this->db->query($sql_sum);
		$data['account_total'] = $query->row()->total;
		
		//总记录条数
		$sql_count = "select count(*) as count from rsh_account as a left join rsh_user as u on a.user_id=u.user_id";
		$sql_count .= $_where;
		$query = $this->db->query($sql_count);
		$data['account_count'] = $query->row()->count;
		return $data;
    }
    
	function get_account_user_list1($data){
		//封装查询内容
		$_select = "a.id, a.user_id, u.username,a.total, a.use_money, 
					a.no_use_money, a.collection";
		
		//封装条件
		$_where = " where 1=1 ";
		if(isset($data['where']['username']) && $data['where']['username'] != ''){
			$_where .= " and u.username like '%".$data['where']['username']."%'";
		}
		
		
		if(isset($data['where']['is_self']) && $data['where']['is_self'] != -1){
			$_where .= " and u.is_self=".$data['where']['is_self'];
		}
		
		$_limit = " limit ".$data['page_start'].",".$data['page_size'];
		//封装sql
		$sql_result = "select $_select from rsh_account as a 
						inner join rsh_user as u on a.user_id=u.user_id
						left join rsh_account_bank as b on a.user_id=b.user_id
						";
		$sql_result .= $_where;
		if(isset($data['limit']) && $data['limit'] == 'all'){
			$query = $this->db->query($sql_result);
			$data['account_list'] = $query->result_array();
			
			if (isset($data['where']['endtime']) && $data['where']['endtime']!=""){
				$dotime2 = strtotime($data['where']['endtime'].' 23:59:59');
				foreach($data['account_list'] as $k => $v){
					$mode = substr(strval($v['user_id']),-1,1); 
					$query = $this->db->query("SELECT * FROM rsh_account_log_$mode WHERE user_id='{$v['user_id']}' AND `add_time`<=$dotime2 ORDER BY add_time desc,id DESC LIMIT 1;");
					$row = $query->row_array();
					$data['account_list'][$k]['total'] = isset($row['total']) ? $row['total'] : 0;
					$data['account_list'][$k]['use_money'] = isset($row['use_money']) ? $row['use_money'] : 0;
					$data['account_list'][$k]['no_use_money'] = isset($row['no_use_money']) ? $row['no_use_money'] : 0;
					$data['account_list'][$k]['collection'] = isset($row['collection']) ? $row['collection'] : 0;
				}
			}
			return $data;
		}else{
			$sql_result .= $_limit;
			$query = $this->db->query($sql_result);
			$data['account_list'] = $query->result_array();
			if (isset($data['where']['endtime']) && $data['where']['endtime'] != ""){
				$dotime2 = strtotime($data['where']['endtime'].' 23:59:59');
				foreach($data['account_list'] as $k => $v){
					$mode = substr(strval($v['user_id']),-1,1); 
					$query = $this->db->query("SELECT * FROM rsh_account_log_$mode WHERE user_id='{$v['user_id']}' AND `add_time`<=$dotime2 ORDER BY add_time desc,id DESC LIMIT 1;");
					$row = $query->row_array();
					$data['account_list'][$k]['total'] = isset($row['total']) ? $row['total'] : 0;
					$data['account_list'][$k]['use_money'] = isset($row['use_money']) ? $row['use_money'] : 0;
					$data['account_list'][$k]['no_use_money'] = isset($row['no_use_money']) ? $row['no_use_money'] : 0;
					$data['account_list'][$k]['collection'] = isset($row['collection']) ? $row['collection'] : 0;
				}
			}
		}
		
		//统计可用金额的总额
		$sql_sum = "select sum(a.use_money) as total from rsh_account as a 
					inner join rsh_user as u on a.user_id=u.user_id
					left join rsh_account_bank as b on a.user_id=b.user_id 
					";
		$sql_sum .= $_where;
		$query = $this->db->query($sql_sum);
		$data['account_total'] = $query->row()->total;
		
		//总记录条数
		$sql_count = "select count(*) as count from rsh_account as a 
						left join rsh_user as u on a.user_id=u.user_id
						left join rsh_account_bank as b on a.user_id=b.user_id 
						";
		$sql_count .= $_where;
		$query = $this->db->query($sql_count);
		$data['account_count'] = $query->row()->count;
		return $data;
	}
	
	

	function get_accountstat_list($data){
		//封装查询内容
		$_select = "t1.user_id, t1.username, t1.realname, t1.is_loan, t2.total, t2.use_money, t2.no_use_money, t2.collection,
					t3.online_recharge, t4.local_recharge, t5.platform_recharge, t6.cash_money, t7.cash_success, t8.recharge_fee, 
					t9.deduct_fee, t10.tender_money, t11.inverst_false, t12.inverst_success, t13.total_collection, t14.total_interest ";
		
		//封装条件
		$_where = " where 1=1 ";
		if(isset($data['where']['username']) && $data['where']['username'] != ''){
			$_where .= " and t1.username like '%".$data['where']['username']."%'";
		}
		if(isset($data['where']['is_loan']) && $data['where']['is_loan'] != -1){
			$_where .= " and t1.is_loan=".$data['where']['is_loan'];
		}
		$_limit = " limit ".$data['page_start'].",".$data['page_size'];
		//封装sql
		$_where1 = " 1=1 ";
		if(isset($data['where']['strtime']) && $data['where']['strtime'] != 0){
			$strtime = strtotime($data['where']['strtime'].' 00:00:00');
			$_where1 .= " and add_time >= ".$strtime;
		}
		if(isset($data['where']['endtime']) && $data['where']['endtime'] != 0){
			$endtime = strtotime($data['where']['endtime'].' 23:59:59');
			$_where1 .= " and add_time <= ".$endtime;
		}
		
		$sql_result = "select $_select from rsh_user as t1 
		left join rsh_account as t2 on t1.user_id=t2.user_id left join 
		(SELECT user_id,SUM(money) AS online_recharge FROM rsh_account_recharge WHERE ".$_where1." AND FTYPE=1 and FSTATUS=1 GROUP BY user_id) AS t3
		ON t1.user_id=t3.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) AS local_recharge FROM rsh_account_recharge WHERE ".$_where1." AND FTYPE=2 and FSTATUS=1 GROUP BY user_id) AS t4
		ON t1.user_id=t4.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) AS platform_recharge FROM rsh_account_recharge WHERE ".$_where1." AND fTYPE=0 and fstatus=1 GROUP BY user_id) AS t5
		ON t1.user_id=t5.user_id LEFT JOIN 
		(SELECT user_id,SUM(total) AS cash_money FROM rsh_account_cash WHERE ".$_where1." AND fstatus=1 GROUP BY user_id) AS t6
		ON t1.user_id=t6.user_id LEFT JOIN 
		(SELECT user_id,SUM(credited) as cash_success FROM rsh_account_cash WHERE ".$_where1." AND fSTATUS=1 GROUP BY user_id) AS t7
		ON t1.user_id=t7.user_id LEFT JOIN 
		(SELECT user_id,SUM(fee) as recharge_fee FROM rsh_account_cash WHERE ".$_where1." AND fSTATUS=1 GROUP BY user_id) AS t8
		ON t1.user_id=t8.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) as deduct_fee FROM rsh_account_log WHERE  ".$_where1." AND FTYPE IN ('scene_ac','vouch_advanced','borrow_kouhui','ac_other') GROUP BY user_id) AS t9
		ON t1.user_id=t9.user_id LEFT JOIN 
		(SELECT user_id,SUM(account) AS tender_money FROM rsh_borrow_tender WHERE ".$_where1."  GROUP BY user_id) AS t10
		ON t1.user_id=t10.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) AS inverst_false FROM rsh_account_log WHERE ".$_where1." AND FTYPE='invest_false' GROUP BY user_id) AS t11
		ON t1.user_id=t11.user_id LEFT JOIN 
		(SELECT user_id,SUM(account) AS inverst_success FROM rsh_borrow_tender WHERE ".$_where1." and borrow_id in (select id from rsh_borrow where fstatus=3) GROUP BY user_id) AS t12
		ON t1.user_id=t12.user_id LEFT JOIN 
		(SELECT user_id,SUM(repay_account) as total_collection FROM rsh_borrow_collection where ".$_where1." GROUP BY user_id) as t13
 		ON t1.user_id=t13.user_id LEFT JOIN 
 		(SELECT user_id,SUM(interest) as total_interest FROM rsh_borrow_collection where ".$_where1." GROUP BY user_id) as t14
 		ON t1.user_id=t14.user_id
		";
		$sql_result .= $_where;
		if(isset($data['limit']) && $data['limit'] == 'all'){
			$query = $this->db->query($sql_result);
			$data['account_list'] = $query->result_array();
			return $data;
		}else{
			$sql_result .= $_limit;	
			$query = $this->db->query($sql_result);
			$data['account_list'] = $query->result_array();
		}
		
		
		//总记录条数
		$sql_count = "select count(*) as count from rsh_user as t1 
		left join rsh_account as t2 on t1.user_id=t2.user_id left join 
		(SELECT user_id,SUM(money) AS online_recharge FROM rsh_account_recharge WHERE ".$_where1." AND FTYPE=1 and FSTATUS=1 GROUP BY user_id) AS t3
		ON t1.user_id=t3.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) AS local_recharge FROM rsh_account_recharge WHERE ".$_where1." AND FTYPE=2 and FSTATUS=1 GROUP BY user_id) AS t4
		ON t1.user_id=t4.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) AS platform_recharge FROM rsh_account_recharge WHERE ".$_where1." AND fTYPE=0 and fstatus=1 GROUP BY user_id) AS t5
		ON t1.user_id=t5.user_id LEFT JOIN 
		(SELECT user_id,SUM(total) AS cash_money FROM rsh_account_cash WHERE ".$_where1." AND fstatus=1 GROUP BY user_id) AS t6
		ON t1.user_id=t6.user_id LEFT JOIN 
		(SELECT user_id,SUM(credited) as cash_success FROM rsh_account_cash WHERE ".$_where1." AND fSTATUS=1 GROUP BY user_id) AS t7
		ON t1.user_id=t7.user_id LEFT JOIN 
		(SELECT user_id,SUM(fee) as recharge_fee FROM rsh_account_cash WHERE ".$_where1." AND fSTATUS=1 GROUP BY user_id) AS t8
		ON t1.user_id=t8.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) as deduct_fee FROM rsh_account_log WHERE  ".$_where1." AND FTYPE IN ('scene_ac','vouch_advanced','borrow_kouhui','ac_other') GROUP BY user_id) AS t9
		ON t1.user_id=t9.user_id LEFT JOIN 
		(SELECT user_id,SUM(account) AS tender_money FROM rsh_borrow_tender WHERE ".$_where1."  GROUP BY user_id) AS t10
		ON t1.user_id=t10.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) AS inverst_false FROM rsh_account_log WHERE ".$_where1." AND FTYPE='invest_false' GROUP BY user_id) AS t11
		ON t1.user_id=t11.user_id LEFT JOIN 
		(SELECT user_id,SUM(account) AS inverst_success FROM rsh_borrow_tender WHERE ".$_where1." and borrow_id in (select id from rsh_borrow where fstatus=3) GROUP BY user_id) AS t12
		ON t1.user_id=t12.user_id LEFT JOIN 
		(SELECT user_id,SUM(repay_account) as total_collection FROM rsh_borrow_collection where ".$_where1." GROUP BY user_id) as t13
 		ON t1.user_id=t13.user_id LEFT JOIN 
 		(SELECT user_id,SUM(interest) as total_interest FROM rsh_borrow_collection where ".$_where1." GROUP BY user_id) as t14
 		ON t1.user_id=t14.user_id
		";
		$sql_count .= $_where;
		$query = $this->db->query($sql_count);
		$data['account_count'] = $query->row()->count;
		return $data;
	}
	
	
	// *******************************
	function init_account($data){
		$this->db->insert('rsh_account', $data);
		return $this->db->insert_id();
	}
	// *******************************
	function get_account($user_id){
		$this->db->where('user_id', $user_id);
		$query = $this->db->get('rsh_account');
		
		// 区分投标冻结额度和提现冻结额度
		$_sql_tender = " select sum(money) as account_tender from rsh_borrow_tender 	where  user_id = {$user_id} and bf_trade_status !=3  ";
		$query_tender = $this->db->query($_sql_tender);
		
		$_sql_cash = "select sum(total) as account_cash from rsh_account_cash where user_id = {$user_id} and fstatus =0  ";
		$query_cash = $this->db->query($_sql_cash);
		
		//$data = Ar
		$data = $query->row_array();
		
		$data['account_cash'] = $query_cash->row()->account_cash;
		$data['account_tender'] = $query_tender->row()->account_tender;
		
		
		return $data;
	}
	// *******************************
	function get_borrow_tender($user_id){
		$this->db->where('user_id', $user_id);
		$query = $this->db->get('rsh_borrow_tender');
		return $query->row_array();
	}
	
	
	function accountlog($data){
		$accountdata['total'] 			= $data['total'];
		$accountdata['use_money'] 		= $data['use_money'];
		$accountdata['no_use_money'] 	= $data['no_use_money'];
		$accountdata['collection'] 		= $data['collection'];
		$accountdata['red_money'] 		= $data['red_money'];
		unset($data['red_money']); 
		
		$user_id = $data['user_id'];
		$mode = substr(strval($user_id),-1,1);
		/*tangc 死锁问题*/
		$this->db->trans_strict(FALSE);
    	$this->db->trans_begin();
    
		$this->db->insert('rsh_account_log_'.$mode, $data);
		$this->db->where('user_id', $user_id);
		$this->db->update('rsh_account', $accountdata);
		if ($this->db->trans_status() === TRUE) {
	        $this->db->trans_commit();
	        return 1;
	    } else {
	        $this->db->trans_rollback();
	        //@todo 异常处理部分
	        return 0;
	    }
	}
	function is_accountlog($data){
		$mode = substr(strval($data['user_id']),-1,1);
		$sql = "select * from rsh_account_log_".$mode." where user_id='".$data['user_id']."' and ftype='".REDPACKAGE."' and remark='".$data['remark']."' limit 1";
		$query = $this->db->query($sql);
		$resr = $query->result_array();
		if(count($resr) > 0) return false;
		else return true;
	}
	
	
	//上面那个 不方便用  事务我放外面做  很多一起
	function addlog($data){
		$ret = false;
		if(isset($data['user_id'])){
			if(isset($data['total']))  $accountdata['total'] = $data['total'];
			if(isset($data['use_money']))  $accountdata['use_money'] 	= $data['use_money'];
			if(isset($data['red_money']))  $accountdata['red_money'] 	= $data['red_money'];
			if(isset($data['no_use_money']))  $accountdata['no_use_money'] 	= $data['no_use_money'];
			if(isset($data['collection']))  $accountdata['collection'] 		= $data['collection'];
			$user_id = $data['user_id'];
			
			$this->db->where('user_id', $user_id);
			$this->db->update('rsh_account', $accountdata);
			$ret = $this->db->affected_rows();
			unset($data['red_money']);
			if($ret){
				//TODO  日志记录 表名获取错误
				//*******************$mode = substr(strval($user_id),-1,1);
				$mode = substr(strval($user_id),-1,1);
				$this->db->insert('rsh_account_log_'.$mode, $data);
				$ret = $this->db->affected_rows();
			}
		}
		return $ret;
	}
	

	//上面那个 不方便用  事务我放外面做  很多一起
	function addredlog($data){
		$ret = false;
		$accountdata['total'] 			= $data['total'];
		$accountdata['use_money'] 		= $data['use_money'];
		$accountdata['red_money'] 		= $data['red_money'];
		$accountdata['no_use_money'] 	= $data['no_use_money'];
		$accountdata['collection'] 		= $data['collection'];
		$user_id = $data['user_id'];
		$this->db->where('user_id', $user_id);
		$this->db->update('rsh_account', $accountdata);
		$ret = $this->db->affected_rows();
		return $ret;
	}
	
	function get_user_borrow_account($user_id){
		$sql = "select fstatus,repayment_account,capital,interest from rsh_borrow_repayment where user_id=$user_id";
		$query = $this->db->query($sql);
		$account_list = $query->result_array();
		$accounta = 0;
		$account0 = 0;
		$account1 = 0;
		foreach($account_list as $account){
			$accounta += $account['capital'];
			if($account['fstatus'] == 0){
				$account0 += $account['repayment_account'];
			}elseif($account['fstatus'] == 1){
				$account1 += $account['repayment_account'];
			}
		}
		
		$sql = "select p1.fstatus,p1.repay_account,p1.capital from 
				rsh_borrow_collection as p1 left join rsh_borrow as p2 on p1.borrow_id=p2.id 
				where p2.ftype=4 and p2.user_id=$user_id";
		$query = $this->db->query($sql);
		$account_list = $query->result_array();
		foreach($account_list as $account){
			$accounta += $account['capital'];
			if($account['fstatus'] == 0){
				$account0 += $account['repay_account'];
			}elseif($account['fstatus'] == 1){
				$account1 += $account['repay_account'];
			}
		}
		$data['accounta'] = $accounta;
		$data['account0'] = $account0;
		$data['account1'] = $account1;
		return $data;
	}
	
	function get_user_borrow_account_capital($user_id){
		$sql = "select fstatus,repayment_account,capital,interest from rsh_borrow_repayment where user_id=$user_id";
		$query = $this->db->query($sql);
		$account_list = $query->result_array();
		$accounta = 0;
		$account0 = 0;
		$account1 = 0;
		foreach($account_list as $account){
			$accounta += $account['capital'];
			if($account['fstatus'] == 0){
				$account0 += $account['capital'];
			}elseif($account['fstatus'] == 1){
				$account1 += $account['capital'];
			}
		}
	
		$sql = "select p1.fstatus,p1.repay_account,p1.capital from
		rsh_borrow_collection as p1 left join rsh_borrow as p2 on p1.borrow_id=p2.id
		where p2.ftype=4 and p2.user_id=$user_id";
		$query = $this->db->query($sql);
		$account_list = $query->result_array();
		foreach($account_list as $account){
		$accounta += $account['capital'];
		if($account['fstatus'] == 0){
		$account0 += $account['capital'];
		}elseif($account['fstatus'] == 1){
		$account1 += $account['capital'];
		}
		}
		$data['accounta'] = $accounta;
		$data['account0'] = $account0;
		$data['account1'] = $account1;
		return $data;
	}
	
	//********************************
	function income_total($user_id){
		//利息
		$total = 0;
		$sql = "select sum(interest)+sum(work_interest) as interest from rsh_borrow_collection where user_id=$user_id  and fstatus=0 and borrow_id in (select id from rsh_borrow where fstatus=3  and repayment_account > repayment_yesaccount)";
		$query = $this->db->query($sql);
		$result = $query->row_array();
		if($result){
			$total += $result['interest'];
		}
		
		
		//TODO 20141201 by liub 
		/*
		//奖励
		$table = 'rsh_account_log';
		$mode = substr(strval($user_id),-1,1);
		$table = $table . "_" . $mode;
		$ftype = AWARD_ADD.','.LATE_COLLECTION.','.RECHARGE_REWARD.','.REPAYMENT_TENDER_REWARD.','.LIMIT_REWARD.','.CHOUJIANG_REWARD.','.INVITE_REWARD;
		$sql = "select sum(money) as total_money from `{$table}` where user_id='{$user_id}' and ftype in ($ftype)";
		$query = $this->db->query($sql);
		$result = $query->row_array();
		$total = 0;
		if($result){
			$total = $result['total_money'];
		}
		//后台充值奖励
		$sql = "select sum(money) as money from rsh_account_recharge where user_id=$user_id and ftype=2 and fstatus=1 and remark like '%奖励%'";
		$query = $this->db->query($sql);
		$result = $query->row_array();
		if($result){
			$total += $result['money'];
		}
		$sql = "select sum(money) as money from rsh_account_recharge where user_id=$user_id and ftype=3 and fstatus=1";
		$query = $this->db->query($sql);
		$result = $query->row_array();
		if($result){
			$total += $result['money'];
		}
		*/
		return $total;
	}
	
	//*****************************
	function tender_total($user_id){
		$total=0;
		$sql = "select sum(money-account) as alinterest from rsh_borrow_tender where user_id=$user_id";
		$query = $this->db->query($sql);
		$result = $query->row_array();
		if($result){
			$total += $result['alinterest'];
		}
		return $total;
	}
	//*****************************
	function allinter($user_id){
		$total=0;
		$sql = "select sum(repayment_yesinterest) as allinter from rsh_borrow_tender where user_id=$user_id and fstatus=1";
		$query = $this->db->query($sql);
		$result = $query->row_array();
		if($result){
			$total += $result['allinter'];
		}
		return $total;
	}
	
	function index_tongji(){
//		$ret = array();
//		$mkey = "index_tongji";
//		$this->mycache->delete($mkey);
//		$ret = $this->mycache->get_array($mkey);
//		//'type' => date('m-d',$date_start),
//		$t = strtotime(date("Y-m-d"))-3600*24;
//		if(isset($ret[0]['type']) && $ret[0]['type']!=date('m-d',$t)){
//			$ret = array();
//		}
		$ret = array();
		$mkey = "index_tongji_".date('Ymd');
		//$this->mycache->delete($mkey);
		$ret = $this->mycache->get_array($mkey);
		//$ret = false;
		if(!$ret){
			
//			//昨日统计
//			$date_start = strtotime(date("Y-m-d"))-3600*24;
//			$date_end = strtotime(date("Y-m-d"));
//			$sql = "select id,ftype,account,repayment_account,is_day,time_limit,time_limit_day,apr,funds from rsh_borrow where (fstatus=3 and repayment_time>={$date_start} and repayment_time<{$date_end} and ftype!=4) or(fstatus=3 and ftype=4 and id in(select DISTINCT borrow_id from rsh_borrow_tender where add_time>={$date_start} and add_time<{$date_end}))";
//			//$list = $mysql->db_fetch_arrays($sql);
//			$query = $this->db->query($sql);
//			$list = $query->result_array();
//			
//			$total_money = 0;
//			$total_lixi = 0;
//			$tmp = array();
//			foreach($list as $key => $val){	
//				if($val['ftype']==4){
//					$s_account = $val['account'];
//					$sql = "select sum(account) as account from rsh_borrow_tender where borrow_id='{$val['id']}' and add_time>={$date_start} and add_time<{$date_end}";
//					//$row = $mysql->db_fetch_array($sql);
//					$query = $this->db->query($sql);
//					$row = $query->row_array();
//					$val['account'] = $row['account'];
//					//$lixi = ($val['repayment_account']-$val['account']) * $row['account'] / $s_account;
//					$lixi = $val['account'] * $val['apr'] / 100 / 12 * $val['time_limit'];
//					$total_lixi = $total_lixi + $lixi;
//				}else{
//					$total_lixi = $total_lixi + $val['repayment_account']-$val['account'];
//				}
//				
//				if($val['is_day']==1){
//					$nh = $val['apr']+ ($val['funds']*30/$val['time_limit_day']*12);
//				}else{
//					$nh = $val['apr']+ ($val['funds']/$val['time_limit']*12);
//				}
//				$tmp[] = array(
//					'nh' => $nh,
//					'money' => $val['account']
//				);
//				$total_money = $total_money + $val['account'];
//			}
//			$avg = 0;
//			foreach($tmp as $val){
//				$b = $val['nh']/$total_money *$val['money'];
//				$avg = $avg + $b;
//			}
//			
//			$ret[0] = array(
//				'type' => date('m-d',$date_start),
//				'account' => round($total_money/10000,2),
//				'interest' => round($total_lixi/10000,2),
//				'apr' => round($avg,2),
//			);
//			
//			//当月统计
//			$date_start = strtotime(date("Y-m-01"));
//			$date_end = strtotime(date("Y-m-d"));
//			
//			$sql = "select id,ftype,account,repayment_account,is_day,time_limit,time_limit_day,apr,funds from rsh_borrow where (fstatus=3 and repayment_time>={$date_start} and repayment_time<{$date_end} and ftype!=4) or(fstatus=3 and ftype=4 and id in(select DISTINCT borrow_id from rsh_borrow_tender where add_time>={$date_start} and add_time<{$date_end}))";
//			//$list = $mysql->db_fetch_arrays($sql);
//			$query = $this->db->query($sql);
//			$list = $query->result_array();
//			
//			$total_money = 0;
//			$total_lixi = 0;
//			$tmp = array();
//			foreach($list as $key => $val){	
//				$total_lixi = $total_lixi + $val['repayment_account']-$val['account'];
//				/*if($val['ftype']==4){
//					$sql = "select sum(account) as account from rsh_borrow_tender where borrow_id='{$val['id']}' and add_time>={$date_start} and add_time<{$date_end}";
//					//$row = $mysql->db_fetch_array($sql);
//					$query = $this->db->query($sql);
//					$row = $query->row_array();
//					$val['account'] = $row['account'];
//				}*/
//				
//				if($val['is_day']==1){
//					$nh = $val['apr']+ ($val['funds']*30/$val['time_limit_day']*12);
//				}else{
//					$nh = $val['apr']+ ($val['funds']/$val['time_limit']*12);
//				}
//				$tmp[] = array(
//					'nh' => $nh,
//					'money' => $val['account']
//				);
//				$total_money = $total_money + $val['account'];
//			}
//			$avg = 0;
//			foreach($tmp as $val){
//				$b = $val['nh']/$total_money *$val['money'];
//				$avg = $avg + $b;
//			}
//			
//			$ret[1] = array(
//				'type' => date('m',$date_start).'月',
//				'account' => round($total_money/10000,2),
//				'interest' => round($total_lixi/10000,2),
//				'apr' => round($avg,2),
//			);
			
			//上月统计
			/*
			$date_start = strtotime("-1 month", strtotime(date('Y-m-01'))); 
			$date_end = strtotime(date("Y-m-01"));
			
			$sql = "select id,ftype,account,repayment_account,is_day,time_limit,time_limit_day,apr,funds from rsh_borrow where (fstatus=3 and repayment_time>={$date_start} and repayment_time<{$date_end} and ftype!=4) or(fstatus=3 and ftype=4 and id in(select DISTINCT borrow_id from rsh_borrow_tender where add_time>={$date_start} and add_time<{$date_end}))";
			//$list = $mysql->db_fetch_arrays($sql);
			$query = $this->db->query($sql);
			$list = $query->result_array();
			
			$total_money = 0;
			$total_lixi = 0;
			$tmp = array();
			foreach($list as $key => $val){	
				$total_lixi = $total_lixi + $val['repayment_account']-$val['account'];
				if($val['ftype']==4){
					$sql = "select sum(account) as account from rsh_borrow_tender where borrow_id='{$val['id']}' and add_time>={$date_start} and add_time<{$date_end}";
					//$row = $mysql->db_fetch_array($sql);
					$query = $this->db->query($sql);
					$row = $query->row_array();
					$val['account'] = $row['account'];
				}
				
				if($val['is_day']==1){
					$nh = $val['apr']+ ($val['funds']*30/$val['time_limit_day']*12);
				}else{
					$nh = $val['apr']+ ($val['funds']/$val['time_limit']*12);
				}
				$tmp[] = array(
					'nh' => $nh,
					'money' => $val['account']
				);
				$total_money = $total_money + $val['account'];
			}
			$avg = 0;
			foreach($tmp as $val){
				$b = $val['nh']/$total_money *$val['money'];
				$avg = $avg + $b;
			}
			
			$ret[2] = array(
				'type' => date('m',$date_start).'月',
				'account' => round($total_money/10000,2),
				'interest' => round($total_lixi/10000,2),
				'apr' => round($avg,2),
			);
			*/
			//总统计
			$date_start = strtotime("2012-01-01");
			$date_end = strtotime(date("Y-m-d"));
			
			$sql = "select * from rsh_borrow where (fstatus=3 and repayment_time>={$date_start} and repayment_time<{$date_end} and ftype!=4) or(fstatus=3 and ftype=4 and id in(select DISTINCT borrow_id from rsh_borrow_tender where add_time>={$date_start} and add_time<{$date_end}))";
			//$list = $mysql->db_fetch_arrays($sql);
			$query = $this->db->query($sql);
			$list = $query->result_array();
			
			$total_money = 0;
			$total_lixi = 0;
			$tmp = array();
			
			foreach($list as $key => $val){	
				$total_lixi = $total_lixi + $val['repayment_account']-$val['account'];
				/*if($val['ftype']==4){
					$sql = "select sum(account) as account from rsh_borrow_tender where borrow_id='{$val['id']}' and add_time>={$date_start} and add_time<{$date_end}";
					//$row = $mysql->db_fetch_array($sql);
					$query = $this->db->query($sql);
					$row = $query->row_array();
					$val['account'] = $row['account'];
				}*/
				
				if($val['is_day']==1){
					$nh = $val['apr']+ ($val['funds']*30/$val['time_limit_day']*12);
				}else{
					$nh = $val['apr']+ ($val['funds']/$val['time_limit']*12);
				}
				$tmp[] = array(
					'nh' => $nh,
					'money' => $val['account']
				);
				$total_money = $total_money + $val['account'];
			}
			$avg = 0;
			foreach($tmp as $val){
				$b = $val['nh']/$total_money *$val['money'];
				$avg = $avg + $b;
			}
			
			//$ret[3] = array(
			$ret = array(
				'type' => '总统计',
				'account' => round($total_money),//'account' => round($total_money/10000,2),
				'interest' => round($total_lixi),//'interest' => round($total_lixi/10000,2),
				'apr' => round($avg,2),
			);
			
			//$mc->set($mkey, serialize($ret),3600*6);
			$this->mycache->set_array($mkey,$ret,3600*24);
		}
		return $ret;
	}
	
	function kefu_customers_stat($kefu_userid){
		$sql = "SELECT p1.kefu_userid,p2.user_id,p2.collection,p3.username,p3.realname,p3.phone 
				FROM rsh_user_vip AS p1 LEFT JOIN rsh_account AS p2 ON 
				p1.user_id=p2.user_id LEFT JOIN rsh_user AS p3 ON 
				p1.user_id=p3.user_id where p1.kefu_userid={$kefu_userid} ORDER BY p2.collection desc limit 30";
		//$list = $mysql->db_fetch_arrays($sql);
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	//TODO 20150209 by liub  首页成交量统计
	function deal_success(){
		//$sql= " select sum(money) as deal_money from rsh_borrow_tender ";
		$sql= " select sum(money) as deal_money from rsh_borrow_tender WHERE user_id not in(10759,10760,10761,10762,10763,10764,10765,10766,10767,10768,10769,10770)";
		$query = $this->db->query($sql);
		return $query->row()->deal_money;
	}
	
	
	//TODO 20150519 by liub  首页成交量统计
	function deal_interest(){
		//$sql= " select sum(interest)+sum(late_interest) as interest from rsh_borrow_collection ";
		
		$sql= " select sum(interest)+sum(late_interest) as interest from rsh_borrow_collection WHERE user_id not in(10759,10760,10761,10762,10763,10764,10765,10766,10767,10768,10769,10770)";
		$query = $this->db->query($sql);
		return $query->row()->interest;
	}
}
=======
<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Account_model extends CI_Model {
    
	function __construct(){
        parent::__construct();
    }
    
    
	function arrays($key){
    	$data = array(
    		'user_loan' 			=> array("0"=>"投资客", "1"=>"借款户"),
    		'user_self' 	=> array("0"=>"客户", "1"=>"马甲"),		
	        'account_type' 			=> array(
					TENDER=>'投标',RECHARGE=>'用户充值',BORROW_FEE=>'借款管理费',CASH_CANCEL=>'取消提现解冻',CASH_FROST=>'提现冻结',
					BORROW_SUCCESS=>'借款入帐',MARGIN=>'保证金',INVEST=>'扣除冻结款',FEE=>'充值手续费',AWARD_LOWER=>'扣除投标奖励',
					AWARD_ADD=>'投标奖励',REPAYMENT=>'借款户还款',INVEST_FALSE=>'投标失败资金返回',RECHARGE_FEE=>'提现手续费',COLLECTION=>'待收金额',
					BORROW_FROST=>'解冻借款担保金',INVEST_REPAYMENT=>'投资人回款',VIP=>'vip会员费',REALNAME=>'实名认证费用',RECHARGE_SUCCESS=>'提现审核通过',
					RECHARGE_FALSE=>'提现失败',SYSTEM_REPAYMENT=>'逾期系统还款',LATE_RATE=>'逾期利息扣除',INVEST_REPAYMENT_WY=>'客户提前还款违约',TICHENG=>'投标提成',
					LATE_COLLECTION=>'逾期收入',SCENE_AC=>'现场认证费用',VOUCH_ADVANCED=>'担保垫付扣费',BORROW_KOUHUI=>'借款人罚金扣回',VOUCH_AWARDPAY=>'担保奖励支付',
					VOUCH_AWARD=>'担保奖励接收',MARGIN_VOUCH=>'担保成功冻结5%',VIDEO=>'视频认证费用',TENDER_MANGE=>'利息管理费用',REPAYMENT_WY=>'提前还款违约',
					INVEST_REPAYMENT_WY=>'客户提前还款违约',AC_OTHER=>'其它扣费',CREDIT_CASH=>'积分兑现',OTHER_FEE=>'其他费用',RECHARGE_REWARD=>'线下充值奖励',
					REPAYMENT_TENDER_REWARD=>'回款再投标奖励',INVITE_REWARD=>'推荐奖励',INVEST_ROLLBACK_REPAYMENT=>"还款回滚操作",INVEST_SINGLE_FALSE=>'用户撤标',
	        		REPEAT_VERIFY_FREEZE=>'重复复审冻结',REPEAT_VERIFY_COLLECTION=>'重复复审待收',REG_MONEY=>'注册礼金',RECHARGE_CHINABANK_REWARD=>'网银在线充值奖励',EXP_MONEY=>'体验金',EXP_MONEY_DEDUCT=>'体验金扣除',
	        		CREDITOR_FEEEZE=>'购买债权资金冻结',CREDITOR_NOFEEEZE=>'购买债权成功扣除冻结款',CREDITOR_SUCCESS=>'出售债权成功入账',CREDITOR_COLL_DECR=>'出售债权待收减少',CREDITOR_COLL_INCR=>'购买债权待收增加',CREDITOR_FAIL=>'购买债权失败解除冻结金',CREDITOR_SUCC_FEE=>'出售债权成功扣除手续费',
	        		INVEST_CANCEL_COLL=>'撤销投标待收扣除',INVEST_CANCEL_ACC=>'撤销投标本金返回',INVEST_CANCEL_AWARD=>'撤销投标奖励扣除',INVEST_FANXIAN=>'投标返现奖励',REDPACKAGE=>'代金券'),
    	);
    	
    	if($key && isset($data[$key])){
    		return $data[$key];
    	}else{
    		return array();
    	}
    }
    
    function get_account_user_list($data){
    	$_select = "a.id, a.user_id, u.username,u.add_time,a.total, a.use_money,
					a.no_use_money, a.collection, a.red_money,t3.online_recharge,t6.cash_money,t6.recharge_fee,t6.count_cash";
    	
   		$_where = " where 1=1 ";
		if(isset($data['where']['username']) && $data['where']['username'] != ''){
			$_where .= " and u.username like '%".$data['where']['username']."%'";
		}
		
		$_limit = " limit ".$data['page_start'].",".$data['page_size'];
		
		$sql_result = "select $_select from rsh_account as a inner join rsh_user as u on a.user_id=u.user_id
						 left join (SELECT user_id,SUM(money) AS online_recharge ,COUNT(id) as count_recharge FROM rsh_account_recharge WHERE  FSTATUS=1 GROUP BY user_id) AS t3 ON a.user_id=t3.user_id
						 LEFT JOIN (SELECT user_id,SUM(total) AS cash_money ,SUM(fee) as recharge_fee,SUM(credited) as cash_success,COUNT(id) as count_cash  FROM rsh_account_cash WHERE  fstatus=1 GROUP BY user_id) AS t6 ON a.user_id=t6.user_id 
						 ";		
		$sql_result .= $_where;
		
		if(isset($data['limit']) && $data['limit'] == 'all'){
				$query = $this->db->query($sql_result);
				$data['account_list'] = $query->result_array();
				return $data;
		}
		
		$sql_result .= $_limit;
		$query = $this->db->query($sql_result);
		$data['account_list']  = $query->result_array();
		
		
		
		//统计可用金额的总额
		$sql_sum = "select sum(a.use_money) as total from rsh_account as a inner join rsh_user as u on a.user_id=u.user_id";
		$sql_sum .= $_where;
		
		$query = $this->db->query($sql_sum);
		$data['account_total'] = $query->row()->total;
		
		//总记录条数
		$sql_count = "select count(*) as count from rsh_account as a left join rsh_user as u on a.user_id=u.user_id";
		$sql_count .= $_where;
		$query = $this->db->query($sql_count);
		$data['account_count'] = $query->row()->count;
		return $data;
    }
    
	function get_account_user_list1($data){
		//封装查询内容
		$_select = "a.id, a.user_id, u.username,a.total, a.use_money, 
					a.no_use_money, a.collection";
		
		//封装条件
		$_where = " where 1=1 ";
		if(isset($data['where']['username']) && $data['where']['username'] != ''){
			$_where .= " and u.username like '%".$data['where']['username']."%'";
		}
		
		
		if(isset($data['where']['is_self']) && $data['where']['is_self'] != -1){
			$_where .= " and u.is_self=".$data['where']['is_self'];
		}
		
		$_limit = " limit ".$data['page_start'].",".$data['page_size'];
		//封装sql
		$sql_result = "select $_select from rsh_account as a 
						inner join rsh_user as u on a.user_id=u.user_id
						left join rsh_account_bank as b on a.user_id=b.user_id
						";
		$sql_result .= $_where;
		if(isset($data['limit']) && $data['limit'] == 'all'){
			$query = $this->db->query($sql_result);
			$data['account_list'] = $query->result_array();
			
			if (isset($data['where']['endtime']) && $data['where']['endtime']!=""){
				$dotime2 = strtotime($data['where']['endtime'].' 23:59:59');
				foreach($data['account_list'] as $k => $v){
					$mode = substr(strval($v['user_id']),-1,1); 
					$query = $this->db->query("SELECT * FROM rsh_account_log_$mode WHERE user_id='{$v['user_id']}' AND `add_time`<=$dotime2 ORDER BY add_time desc,id DESC LIMIT 1;");
					$row = $query->row_array();
					$data['account_list'][$k]['total'] = isset($row['total']) ? $row['total'] : 0;
					$data['account_list'][$k]['use_money'] = isset($row['use_money']) ? $row['use_money'] : 0;
					$data['account_list'][$k]['no_use_money'] = isset($row['no_use_money']) ? $row['no_use_money'] : 0;
					$data['account_list'][$k]['collection'] = isset($row['collection']) ? $row['collection'] : 0;
				}
			}
			return $data;
		}else{
			$sql_result .= $_limit;
			$query = $this->db->query($sql_result);
			$data['account_list'] = $query->result_array();
			if (isset($data['where']['endtime']) && $data['where']['endtime'] != ""){
				$dotime2 = strtotime($data['where']['endtime'].' 23:59:59');
				foreach($data['account_list'] as $k => $v){
					$mode = substr(strval($v['user_id']),-1,1); 
					$query = $this->db->query("SELECT * FROM rsh_account_log_$mode WHERE user_id='{$v['user_id']}' AND `add_time`<=$dotime2 ORDER BY add_time desc,id DESC LIMIT 1;");
					$row = $query->row_array();
					$data['account_list'][$k]['total'] = isset($row['total']) ? $row['total'] : 0;
					$data['account_list'][$k]['use_money'] = isset($row['use_money']) ? $row['use_money'] : 0;
					$data['account_list'][$k]['no_use_money'] = isset($row['no_use_money']) ? $row['no_use_money'] : 0;
					$data['account_list'][$k]['collection'] = isset($row['collection']) ? $row['collection'] : 0;
				}
			}
		}
		
		//统计可用金额的总额
		$sql_sum = "select sum(a.use_money) as total from rsh_account as a 
					inner join rsh_user as u on a.user_id=u.user_id
					left join rsh_account_bank as b on a.user_id=b.user_id 
					";
		$sql_sum .= $_where;
		$query = $this->db->query($sql_sum);
		$data['account_total'] = $query->row()->total;
		
		//总记录条数
		$sql_count = "select count(*) as count from rsh_account as a 
						left join rsh_user as u on a.user_id=u.user_id
						left join rsh_account_bank as b on a.user_id=b.user_id 
						";
		$sql_count .= $_where;
		$query = $this->db->query($sql_count);
		$data['account_count'] = $query->row()->count;
		return $data;
	}
	
	

	function get_accountstat_list($data){
		//封装查询内容
		$_select = "t1.user_id, t1.username, t1.realname, t1.is_loan, t2.total, t2.use_money, t2.no_use_money, t2.collection,
					t3.online_recharge, t4.local_recharge, t5.platform_recharge, t6.cash_money, t7.cash_success, t8.recharge_fee, 
					t9.deduct_fee, t10.tender_money, t11.inverst_false, t12.inverst_success, t13.total_collection, t14.total_interest ";
		
		//封装条件
		$_where = " where 1=1 ";
		if(isset($data['where']['username']) && $data['where']['username'] != ''){
			$_where .= " and t1.username like '%".$data['where']['username']."%'";
		}
		if(isset($data['where']['is_loan']) && $data['where']['is_loan'] != -1){
			$_where .= " and t1.is_loan=".$data['where']['is_loan'];
		}
		$_limit = " limit ".$data['page_start'].",".$data['page_size'];
		//封装sql
		$_where1 = " 1=1 ";
		if(isset($data['where']['strtime']) && $data['where']['strtime'] != 0){
			$strtime = strtotime($data['where']['strtime'].' 00:00:00');
			$_where1 .= " and add_time >= ".$strtime;
		}
		if(isset($data['where']['endtime']) && $data['where']['endtime'] != 0){
			$endtime = strtotime($data['where']['endtime'].' 23:59:59');
			$_where1 .= " and add_time <= ".$endtime;
		}
		
		$sql_result = "select $_select from rsh_user as t1 
		left join rsh_account as t2 on t1.user_id=t2.user_id left join 
		(SELECT user_id,SUM(money) AS online_recharge FROM rsh_account_recharge WHERE ".$_where1." AND FTYPE=1 and FSTATUS=1 GROUP BY user_id) AS t3
		ON t1.user_id=t3.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) AS local_recharge FROM rsh_account_recharge WHERE ".$_where1." AND FTYPE=2 and FSTATUS=1 GROUP BY user_id) AS t4
		ON t1.user_id=t4.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) AS platform_recharge FROM rsh_account_recharge WHERE ".$_where1." AND fTYPE=0 and fstatus=1 GROUP BY user_id) AS t5
		ON t1.user_id=t5.user_id LEFT JOIN 
		(SELECT user_id,SUM(total) AS cash_money FROM rsh_account_cash WHERE ".$_where1." AND fstatus=1 GROUP BY user_id) AS t6
		ON t1.user_id=t6.user_id LEFT JOIN 
		(SELECT user_id,SUM(credited) as cash_success FROM rsh_account_cash WHERE ".$_where1." AND fSTATUS=1 GROUP BY user_id) AS t7
		ON t1.user_id=t7.user_id LEFT JOIN 
		(SELECT user_id,SUM(fee) as recharge_fee FROM rsh_account_cash WHERE ".$_where1." AND fSTATUS=1 GROUP BY user_id) AS t8
		ON t1.user_id=t8.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) as deduct_fee FROM rsh_account_log WHERE  ".$_where1." AND FTYPE IN ('scene_ac','vouch_advanced','borrow_kouhui','ac_other') GROUP BY user_id) AS t9
		ON t1.user_id=t9.user_id LEFT JOIN 
		(SELECT user_id,SUM(account) AS tender_money FROM rsh_borrow_tender WHERE ".$_where1."  GROUP BY user_id) AS t10
		ON t1.user_id=t10.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) AS inverst_false FROM rsh_account_log WHERE ".$_where1." AND FTYPE='invest_false' GROUP BY user_id) AS t11
		ON t1.user_id=t11.user_id LEFT JOIN 
		(SELECT user_id,SUM(account) AS inverst_success FROM rsh_borrow_tender WHERE ".$_where1." and borrow_id in (select id from rsh_borrow where fstatus=3) GROUP BY user_id) AS t12
		ON t1.user_id=t12.user_id LEFT JOIN 
		(SELECT user_id,SUM(repay_account) as total_collection FROM rsh_borrow_collection where ".$_where1." GROUP BY user_id) as t13
 		ON t1.user_id=t13.user_id LEFT JOIN 
 		(SELECT user_id,SUM(interest) as total_interest FROM rsh_borrow_collection where ".$_where1." GROUP BY user_id) as t14
 		ON t1.user_id=t14.user_id
		";
		$sql_result .= $_where;
		if(isset($data['limit']) && $data['limit'] == 'all'){
			$query = $this->db->query($sql_result);
			$data['account_list'] = $query->result_array();
			return $data;
		}else{
			$sql_result .= $_limit;	
			$query = $this->db->query($sql_result);
			$data['account_list'] = $query->result_array();
		}
		
		
		//总记录条数
		$sql_count = "select count(*) as count from rsh_user as t1 
		left join rsh_account as t2 on t1.user_id=t2.user_id left join 
		(SELECT user_id,SUM(money) AS online_recharge FROM rsh_account_recharge WHERE ".$_where1." AND FTYPE=1 and FSTATUS=1 GROUP BY user_id) AS t3
		ON t1.user_id=t3.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) AS local_recharge FROM rsh_account_recharge WHERE ".$_where1." AND FTYPE=2 and FSTATUS=1 GROUP BY user_id) AS t4
		ON t1.user_id=t4.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) AS platform_recharge FROM rsh_account_recharge WHERE ".$_where1." AND fTYPE=0 and fstatus=1 GROUP BY user_id) AS t5
		ON t1.user_id=t5.user_id LEFT JOIN 
		(SELECT user_id,SUM(total) AS cash_money FROM rsh_account_cash WHERE ".$_where1." AND fstatus=1 GROUP BY user_id) AS t6
		ON t1.user_id=t6.user_id LEFT JOIN 
		(SELECT user_id,SUM(credited) as cash_success FROM rsh_account_cash WHERE ".$_where1." AND fSTATUS=1 GROUP BY user_id) AS t7
		ON t1.user_id=t7.user_id LEFT JOIN 
		(SELECT user_id,SUM(fee) as recharge_fee FROM rsh_account_cash WHERE ".$_where1." AND fSTATUS=1 GROUP BY user_id) AS t8
		ON t1.user_id=t8.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) as deduct_fee FROM rsh_account_log WHERE  ".$_where1." AND FTYPE IN ('scene_ac','vouch_advanced','borrow_kouhui','ac_other') GROUP BY user_id) AS t9
		ON t1.user_id=t9.user_id LEFT JOIN 
		(SELECT user_id,SUM(account) AS tender_money FROM rsh_borrow_tender WHERE ".$_where1."  GROUP BY user_id) AS t10
		ON t1.user_id=t10.user_id LEFT JOIN 
		(SELECT user_id,SUM(money) AS inverst_false FROM rsh_account_log WHERE ".$_where1." AND FTYPE='invest_false' GROUP BY user_id) AS t11
		ON t1.user_id=t11.user_id LEFT JOIN 
		(SELECT user_id,SUM(account) AS inverst_success FROM rsh_borrow_tender WHERE ".$_where1." and borrow_id in (select id from rsh_borrow where fstatus=3) GROUP BY user_id) AS t12
		ON t1.user_id=t12.user_id LEFT JOIN 
		(SELECT user_id,SUM(repay_account) as total_collection FROM rsh_borrow_collection where ".$_where1." GROUP BY user_id) as t13
 		ON t1.user_id=t13.user_id LEFT JOIN 
 		(SELECT user_id,SUM(interest) as total_interest FROM rsh_borrow_collection where ".$_where1." GROUP BY user_id) as t14
 		ON t1.user_id=t14.user_id
		";
		$sql_count .= $_where;
		$query = $this->db->query($sql_count);
		$data['account_count'] = $query->row()->count;
		return $data;
	}
	
	
	// *******************************
	function init_account($data){
		$this->db->insert('rsh_account', $data);
		return $this->db->insert_id();
	}
	// *******************************
	function get_account($user_id){
		$this->db->where('user_id', $user_id);
		$query = $this->db->get('rsh_account');
		
		// 区分投标冻结额度和提现冻结额度
		$_sql_tender = " select sum(money) as account_tender from rsh_borrow_tender 	where  user_id = {$user_id} and bf_trade_status !=3  ";
		$query_tender = $this->db->query($_sql_tender);
		
		$_sql_cash = "select sum(total) as account_cash from rsh_account_cash where user_id = {$user_id} and fstatus =0  ";
		$query_cash = $this->db->query($_sql_cash);
		
		//$data = Ar
		$data = $query->row_array();
		
		$data['account_cash'] = $query_cash->row()->account_cash;
		$data['account_tender'] = $query_tender->row()->account_tender;
		
		
		return $data;
	}
	// *******************************
	function get_borrow_tender($user_id){
		$this->db->where('user_id', $user_id);
		$query = $this->db->get('rsh_borrow_tender');
		return $query->row_array();
	}
	
	
	function accountlog($data){
		$accountdata['total'] 			= $data['total'];
		$accountdata['use_money'] 		= $data['use_money'];
		$accountdata['no_use_money'] 	= $data['no_use_money'];
		$accountdata['collection'] 		= $data['collection'];
		$accountdata['red_money'] 		= $data['red_money'];
		unset($data['red_money']); 
		
		$user_id = $data['user_id'];
		$mode = substr(strval($user_id),-1,1);
		/*tangc 死锁问题*/
		$this->db->trans_strict(FALSE);
    	$this->db->trans_begin();
    
		$this->db->insert('rsh_account_log_'.$mode, $data);
		$this->db->where('user_id', $user_id);
		$this->db->update('rsh_account', $accountdata);
		if ($this->db->trans_status() === TRUE) {
	        $this->db->trans_commit();
	        return 1;
	    } else {
	        $this->db->trans_rollback();
	        //@todo 异常处理部分
	        return 0;
	    }
	}
	function is_accountlog($data){
		$mode = substr(strval($data['user_id']),-1,1);
		$sql = "select * from rsh_account_log_".$mode." where user_id='".$data['user_id']."' and ftype='".REDPACKAGE."' and remark='".$data['remark']."' limit 1";
		$query = $this->db->query($sql);
		$resr = $query->result_array();
		if(count($resr) > 0) return false;
		else return true;
	}
	
	
	//上面那个 不方便用  事务我放外面做  很多一起
	function addlog($data){
		$ret = false;
		if(isset($data['user_id'])){
			if(isset($data['total']))  $accountdata['total'] = $data['total'];
			if(isset($data['use_money']))  $accountdata['use_money'] 	= $data['use_money'];
			if(isset($data['red_money']))  $accountdata['red_money'] 	= $data['red_money'];
			if(isset($data['no_use_money']))  $accountdata['no_use_money'] 	= $data['no_use_money'];
			if(isset($data['collection']))  $accountdata['collection'] 		= $data['collection'];
			$user_id = $data['user_id'];
			
			$this->db->where('user_id', $user_id);
			$this->db->update('rsh_account', $accountdata);
			$ret = $this->db->affected_rows();
			unset($data['red_money']);
			if($ret){
				//TODO  日志记录 表名获取错误
				//*******************$mode = substr(strval($user_id),-1,1);
				$mode = substr(strval($user_id),-1,1);
				$this->db->insert('rsh_account_log_'.$mode, $data);
				$ret = $this->db->affected_rows();
			}
		}
		return $ret;
	}
	

	//上面那个 不方便用  事务我放外面做  很多一起
	function addredlog($data){
		$ret = false;
		$accountdata['total'] 			= $data['total'];
		$accountdata['use_money'] 		= $data['use_money'];
		$accountdata['red_money'] 		= $data['red_money'];
		$accountdata['no_use_money'] 	= $data['no_use_money'];
		$accountdata['collection'] 		= $data['collection'];
		$user_id = $data['user_id'];
		$this->db->where('user_id', $user_id);
		$this->db->update('rsh_account', $accountdata);
		$ret = $this->db->affected_rows();
		return $ret;
	}
	
	function get_user_borrow_account($user_id){
		$sql = "select fstatus,repayment_account,capital,interest from rsh_borrow_repayment where user_id=$user_id";
		$query = $this->db->query($sql);
		$account_list = $query->result_array();
		$accounta = 0;
		$account0 = 0;
		$account1 = 0;
		foreach($account_list as $account){
			$accounta += $account['capital'];
			if($account['fstatus'] == 0){
				$account0 += $account['repayment_account'];
			}elseif($account['fstatus'] == 1){
				$account1 += $account['repayment_account'];
			}
		}
		
		$sql = "select p1.fstatus,p1.repay_account,p1.capital from 
				rsh_borrow_collection as p1 left join rsh_borrow as p2 on p1.borrow_id=p2.id 
				where p2.ftype=4 and p2.user_id=$user_id";
		$query = $this->db->query($sql);
		$account_list = $query->result_array();
		foreach($account_list as $account){
			$accounta += $account['capital'];
			if($account['fstatus'] == 0){
				$account0 += $account['repay_account'];
			}elseif($account['fstatus'] == 1){
				$account1 += $account['repay_account'];
			}
		}
		$data['accounta'] = $accounta;
		$data['account0'] = $account0;
		$data['account1'] = $account1;
		return $data;
	}
	
	function get_user_borrow_account_capital($user_id){
		$sql = "select fstatus,repayment_account,capital,interest from rsh_borrow_repayment where user_id=$user_id";
		$query = $this->db->query($sql);
		$account_list = $query->result_array();
		$accounta = 0;
		$account0 = 0;
		$account1 = 0;
		foreach($account_list as $account){
			$accounta += $account['capital'];
			if($account['fstatus'] == 0){
				$account0 += $account['capital'];
			}elseif($account['fstatus'] == 1){
				$account1 += $account['capital'];
			}
		}
	
		$sql = "select p1.fstatus,p1.repay_account,p1.capital from
		rsh_borrow_collection as p1 left join rsh_borrow as p2 on p1.borrow_id=p2.id
		where p2.ftype=4 and p2.user_id=$user_id";
		$query = $this->db->query($sql);
		$account_list = $query->result_array();
		foreach($account_list as $account){
		$accounta += $account['capital'];
		if($account['fstatus'] == 0){
		$account0 += $account['capital'];
		}elseif($account['fstatus'] == 1){
		$account1 += $account['capital'];
		}
		}
		$data['accounta'] = $accounta;
		$data['account0'] = $account0;
		$data['account1'] = $account1;
		return $data;
	}
	
	//********************************
	function income_total($user_id){
		//利息
		$total = 0;
		$sql = "select sum(interest)+sum(work_interest) as interest from rsh_borrow_collection where user_id=$user_id  and fstatus=0 and borrow_id in (select id from rsh_borrow where fstatus=3  and repayment_account > repayment_yesaccount)";
		$query = $this->db->query($sql);
		$result = $query->row_array();
		if($result){
			$total += $result['interest'];
		}
		
		
		//TODO 20141201 by liub 
		/**
		//奖励
		$table = 'rsh_account_log';
		$mode = substr(strval($user_id),-1,1);
		$table = $table . "_" . $mode;
		$ftype = AWARD_ADD.','.LATE_COLLECTION.','.RECHARGE_REWARD.','.REPAYMENT_TENDER_REWARD.','.LIMIT_REWARD.','.CHOUJIANG_REWARD.','.INVITE_REWARD;
		$sql = "select sum(money) as total_money from `{$table}` where user_id='{$user_id}' and ftype in ($ftype)";
		$query = $this->db->query($sql);
		$result = $query->row_array();
		$total = 0;
		if($result){
			$total = $result['total_money'];
		}
		//后台充值奖励
		$sql = "select sum(money) as money from rsh_account_recharge where user_id=$user_id and ftype=2 and fstatus=1 and remark like '%奖励%'";
		$query = $this->db->query($sql);
		$result = $query->row_array();
		if($result){
			$total += $result['money'];
		}
		$sql = "select sum(money) as money from rsh_account_recharge where user_id=$user_id and ftype=3 and fstatus=1";
		$query = $this->db->query($sql);
		$result = $query->row_array();
		if($result){
			$total += $result['money'];
		}
		*/
		return $total;
	}
	
	//*****************************
	function tender_total($user_id){
		$total=0;
		$sql = "select sum(money-account) as alinterest from rsh_borrow_tender where user_id=$user_id";
		$query = $this->db->query($sql);
		$result = $query->row_array();
		if($result){
			$total += $result['alinterest'];
		}
		return $total;
	}
	//*****************************
	function allinter($user_id){
		$total=0;
		$sql = "select sum(repayment_yesinterest) as allinter from rsh_borrow_tender where user_id=$user_id and fstatus=1";
		$query = $this->db->query($sql);
		$result = $query->row_array();
		if($result){
			$total += $result['allinter'];
		}
		return $total;
	}
	
	function index_tongji(){
//		$ret = array();
//		$mkey = "index_tongji";
//		$this->mycache->delete($mkey);
//		$ret = $this->mycache->get_array($mkey);
//		//'type' => date('m-d',$date_start),
//		$t = strtotime(date("Y-m-d"))-3600*24;
//		if(isset($ret[0]['type']) && $ret[0]['type']!=date('m-d',$t)){
//			$ret = array();
//		}
		$ret = array();
		$mkey = "index_tongji_".date('Ymd');
		//$this->mycache->delete($mkey);
		$ret = $this->mycache->get_array($mkey);
		//$ret = false;
		if(!$ret){
			
//			//昨日统计
//			$date_start = strtotime(date("Y-m-d"))-3600*24;
//			$date_end = strtotime(date("Y-m-d"));
//			$sql = "select id,ftype,account,repayment_account,is_day,time_limit,time_limit_day,apr,funds from rsh_borrow where (fstatus=3 and repayment_time>={$date_start} and repayment_time<{$date_end} and ftype!=4) or(fstatus=3 and ftype=4 and id in(select DISTINCT borrow_id from rsh_borrow_tender where add_time>={$date_start} and add_time<{$date_end}))";
//			//$list = $mysql->db_fetch_arrays($sql);
//			$query = $this->db->query($sql);
//			$list = $query->result_array();
//			
//			$total_money = 0;
//			$total_lixi = 0;
//			$tmp = array();
//			foreach($list as $key => $val){	
//				if($val['ftype']==4){
//					$s_account = $val['account'];
//					$sql = "select sum(account) as account from rsh_borrow_tender where borrow_id='{$val['id']}' and add_time>={$date_start} and add_time<{$date_end}";
//					//$row = $mysql->db_fetch_array($sql);
//					$query = $this->db->query($sql);
//					$row = $query->row_array();
//					$val['account'] = $row['account'];
//					//$lixi = ($val['repayment_account']-$val['account']) * $row['account'] / $s_account;
//					$lixi = $val['account'] * $val['apr'] / 100 / 12 * $val['time_limit'];
//					$total_lixi = $total_lixi + $lixi;
//				}else{
//					$total_lixi = $total_lixi + $val['repayment_account']-$val['account'];
//				}
//				
//				if($val['is_day']==1){
//					$nh = $val['apr']+ ($val['funds']*30/$val['time_limit_day']*12);
//				}else{
//					$nh = $val['apr']+ ($val['funds']/$val['time_limit']*12);
//				}
//				$tmp[] = array(
//					'nh' => $nh,
//					'money' => $val['account']
//				);
//				$total_money = $total_money + $val['account'];
//			}
//			$avg = 0;
//			foreach($tmp as $val){
//				$b = $val['nh']/$total_money *$val['money'];
//				$avg = $avg + $b;
//			}
//			
//			$ret[0] = array(
//				'type' => date('m-d',$date_start),
//				'account' => round($total_money/10000,2),
//				'interest' => round($total_lixi/10000,2),
//				'apr' => round($avg,2),
//			);
//			
//			//当月统计
//			$date_start = strtotime(date("Y-m-01"));
//			$date_end = strtotime(date("Y-m-d"));
//			
//			$sql = "select id,ftype,account,repayment_account,is_day,time_limit,time_limit_day,apr,funds from rsh_borrow where (fstatus=3 and repayment_time>={$date_start} and repayment_time<{$date_end} and ftype!=4) or(fstatus=3 and ftype=4 and id in(select DISTINCT borrow_id from rsh_borrow_tender where add_time>={$date_start} and add_time<{$date_end}))";
//			//$list = $mysql->db_fetch_arrays($sql);
//			$query = $this->db->query($sql);
//			$list = $query->result_array();
//			
//			$total_money = 0;
//			$total_lixi = 0;
//			$tmp = array();
//			foreach($list as $key => $val){	
//				$total_lixi = $total_lixi + $val['repayment_account']-$val['account'];
//				/*if($val['ftype']==4){
//					$sql = "select sum(account) as account from rsh_borrow_tender where borrow_id='{$val['id']}' and add_time>={$date_start} and add_time<{$date_end}";
//					//$row = $mysql->db_fetch_array($sql);
//					$query = $this->db->query($sql);
//					$row = $query->row_array();
//					$val['account'] = $row['account'];
//				}*/
//				
//				if($val['is_day']==1){
//					$nh = $val['apr']+ ($val['funds']*30/$val['time_limit_day']*12);
//				}else{
//					$nh = $val['apr']+ ($val['funds']/$val['time_limit']*12);
//				}
//				$tmp[] = array(
//					'nh' => $nh,
//					'money' => $val['account']
//				);
//				$total_money = $total_money + $val['account'];
//			}
//			$avg = 0;
//			foreach($tmp as $val){
//				$b = $val['nh']/$total_money *$val['money'];
//				$avg = $avg + $b;
//			}
//			
//			$ret[1] = array(
//				'type' => date('m',$date_start).'月',
//				'account' => round($total_money/10000,2),
//				'interest' => round($total_lixi/10000,2),
//				'apr' => round($avg,2),
//			);
			
			//上月统计
			/*
			$date_start = strtotime("-1 month", strtotime(date('Y-m-01'))); 
			$date_end = strtotime(date("Y-m-01"));
			
			$sql = "select id,ftype,account,repayment_account,is_day,time_limit,time_limit_day,apr,funds from rsh_borrow where (fstatus=3 and repayment_time>={$date_start} and repayment_time<{$date_end} and ftype!=4) or(fstatus=3 and ftype=4 and id in(select DISTINCT borrow_id from rsh_borrow_tender where add_time>={$date_start} and add_time<{$date_end}))";
			//$list = $mysql->db_fetch_arrays($sql);
			$query = $this->db->query($sql);
			$list = $query->result_array();
			
			$total_money = 0;
			$total_lixi = 0;
			$tmp = array();
			foreach($list as $key => $val){	
				$total_lixi = $total_lixi + $val['repayment_account']-$val['account'];
				if($val['ftype']==4){
					$sql = "select sum(account) as account from rsh_borrow_tender where borrow_id='{$val['id']}' and add_time>={$date_start} and add_time<{$date_end}";
					//$row = $mysql->db_fetch_array($sql);
					$query = $this->db->query($sql);
					$row = $query->row_array();
					$val['account'] = $row['account'];
				}
				
				if($val['is_day']==1){
					$nh = $val['apr']+ ($val['funds']*30/$val['time_limit_day']*12);
				}else{
					$nh = $val['apr']+ ($val['funds']/$val['time_limit']*12);
				}
				$tmp[] = array(
					'nh' => $nh,
					'money' => $val['account']
				);
				$total_money = $total_money + $val['account'];
			}
			$avg = 0;
			foreach($tmp as $val){
				$b = $val['nh']/$total_money *$val['money'];
				$avg = $avg + $b;
			}
			
			$ret[2] = array(
				'type' => date('m',$date_start).'月',
				'account' => round($total_money/10000,2),
				'interest' => round($total_lixi/10000,2),
				'apr' => round($avg,2),
			);
			*/
			//总统计
			$date_start = strtotime("2012-01-01");
			$date_end = strtotime(date("Y-m-d"));
			
			$sql = "select * from rsh_borrow where (fstatus=3 and repayment_time>={$date_start} and repayment_time<{$date_end} and ftype!=4) or(fstatus=3 and ftype=4 and id in(select DISTINCT borrow_id from rsh_borrow_tender where add_time>={$date_start} and add_time<{$date_end}))";
			//$list = $mysql->db_fetch_arrays($sql);
			$query = $this->db->query($sql);
			$list = $query->result_array();
			
			$total_money = 0;
			$total_lixi = 0;
			$tmp = array();
			
			foreach($list as $key => $val){	
				$total_lixi = $total_lixi + $val['repayment_account']-$val['account'];
				/*if($val['ftype']==4){
					$sql = "select sum(account) as account from rsh_borrow_tender where borrow_id='{$val['id']}' and add_time>={$date_start} and add_time<{$date_end}";
					//$row = $mysql->db_fetch_array($sql);
					$query = $this->db->query($sql);
					$row = $query->row_array();
					$val['account'] = $row['account'];
				}*/
				
				if($val['is_day']==1){
					$nh = $val['apr']+ ($val['funds']*30/$val['time_limit_day']*12);
				}else{
					$nh = $val['apr']+ ($val['funds']/$val['time_limit']*12);
				}
				$tmp[] = array(
					'nh' => $nh,
					'money' => $val['account']
				);
				$total_money = $total_money + $val['account'];
			}
			$avg = 0;
			foreach($tmp as $val){
				$b = $val['nh']/$total_money *$val['money'];
				$avg = $avg + $b;
			}
			
			//$ret[3] = array(
			$ret = array(
				'type' => '总统计',
				'account' => round($total_money),//'account' => round($total_money/10000,2),
				'interest' => round($total_lixi),//'interest' => round($total_lixi/10000,2),
				'apr' => round($avg,2),
			);
			
			//$mc->set($mkey, serialize($ret),3600*6);
			$this->mycache->set_array($mkey,$ret,3600*24);
		}
		return $ret;
	}
	
	function kefu_customers_stat($kefu_userid){
		$sql = "SELECT p1.kefu_userid,p2.user_id,p2.collection,p3.username,p3.realname,p3.phone 
				FROM rsh_user_vip AS p1 LEFT JOIN rsh_account AS p2 ON 
				p1.user_id=p2.user_id LEFT JOIN rsh_user AS p3 ON 
				p1.user_id=p3.user_id where p1.kefu_userid={$kefu_userid} ORDER BY p2.collection desc limit 30";
		//$list = $mysql->db_fetch_arrays($sql);
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	//TODO 20150209 by liub  首页成交量统计
	function deal_success(){
		//$sql= " select sum(money) as deal_money from rsh_borrow_tender ";
		$sql= " select sum(money) as deal_money from rsh_borrow_tender WHERE user_id not in(10759,10760,10761,10762,10763,10764,10765,10766,10767,10768,10769,10770)";
		$query = $this->db->query($sql);
		return $query->row()->deal_money;
	}
	
	
	//TODO 20150519 by liub  首页成交量统计
	function deal_interest(){
		//$sql= " select sum(interest)+sum(late_interest) as interest from rsh_borrow_collection ";
		$sql= " select sum(interest)+sum(late_interest) as interest from rsh_borrow_collection WHERE user_id not in(10759,10760,10761,10762,10763,10764,10765,10766,10767,10768,10769,10770)";
		$query = $this->db->query($sql);
		return $query->row()->interest;
	}
}
>>>>>>> d47623b610d79a4eeb489500830c00d70933f414
